# -*- encoding: utf-8 -*-

import mysql.connector
import logging
logger = logging.getLogger()
from six import itervalues

class MysqlBase(object):

	placeholder = '%s'

	def __init__(self, host='192.168.56.103', port=3306, database='hotel',
                 user='root', passwd='123456'):
		self.conn = mysql.connector.connect(user=user, password=passwd, host=host, port=port, autocommit=True)
		self.dbcur = self.conn.cursor()
		if database not in [x[0] for x in self._execute('show databases')]:
			self._execute('CREATE DATABASE %s' % database)
		
		self.conn.database = database
			
		self._init_db()		

	def _init_db(self):
	        #初始化创建数据库
			# 表结构为
			# hotel:
			#	poiId: 		int	 
			#   hotelname: 	varchar(20)   酒店名称   
			#  	fx:         varchar(20)   酒店房型:  豪华型、行政房 
			#   zc:         varchar(20)   酒店早餐:  无早餐／单人早餐
			#   kc:         varchar(20)   酒店库存:  无限/清空
			#   price:      varchar(20)   酒店价格:  涨10/跌10
			#   price_change varchar(20)  酒店价格变化  比较上次爬虫价格变化 
			#  	updatetime   int          更新时间戳 
			logger.info("初始化数据库开始")
			self.dbcur.execute('create table IF NOT EXISTS hotel (poiId int, hotelname varchar(20), fx varchar(20), zc varchar(20), kc varchar(20),  price varchar(20), price_change varchar(20), updatetime int ) ENGINE=InnoDB CHARSET=utf8  ')
			logger.info("初始化数据库结束")
	

	@property
	def close(self):
		self.dbcur.close()	
		self.conn.close()

	def _execute(self, sql_query, values=[]):
		logger.debug("执行sql语句:%s,其中变量为%s :开始", (sql_query, values))
		self.dbcur.execute(sql_query, values)	
		logger.debug("执行sql语句:%s,其中变量为%s :结束", (sql_query, values))
		return self.dbcur

	def insert(self, tablename=None, **values):
		logger.debug("执行插入语句开始")
		if values:
			_keys = ", ".join((k for k in values))
			_values = ", ".join([self.placeholder, ] * len(values))
			sql_query = "INSERT INTO %s (%s) VALUES (%s)" % (tablename, _keys, _values)
		else:
			sql_query = "INSERT INTO %s DEFAULT VALUES" % tablename
		logger.info("<sql: %s>", sql_query)
		if values:
			self.dbcur = self._execute(sql_query, list(itervalues(values)))
		else:
			self.dbcur = self._execute(sql_query)
		return self.dbcur.lastrowid

		logger.debug("执行插入语句结束")

	def update(self, tablename=None, where="1=0", where_values=[], **values):
		_key_values = ", ".join(["%s = %s" % (k, self.placeholder) for k in values])
		sql_query = "UPDATE %s SET %s WHERE %s" % (tablename, _key_values, where)
		logger.info("<sql: %s>", sql_query)
		return self._execute(sql_query, list(itervalues(values)) + list(where_values))


	def select(self, tablename=None, what="*", where="", where_values=[], offset=0, limit=None):
		if isinstance(what, list) or isinstance(what, tuple) or what is None:
			what = ','.join(self.escape(f) for f in what) if what else '*'
		sql_query = "SELECT %s FROM %s" % (what, tablename)
		if where:
			sql_query += " WHERE %s" % where
		if limit:
			sql_query += " LIMIT %d, %d" % (offset, limit)
		logger.info("<sql: %s>", sql_query)
		dbcur = self._execute(sql_query, where_values)
		fields = [f[0] for f in dbcur.description]
	
		for row in dbcur:
			yield dict(zip(fields, row))




			
if __name__ == "__main__":
	db = MysqlBase()
	db.insert(tablename=u"hotel", poiId=111, fx="faf")
	#db.update(u"hotel","poiId=12313", fx="--大床房--")
	print db.select(u"hotel",where='poiId=111 and fx="faf"', what="poiId, fx").next()['poiId']
	

